﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
// Krzysztof Heim
// pktools.codeplex.com

namespace PKTools.Database
{
    public abstract class Sql : IDisposable
    {
        static readonly string
            defaultConnection;
        protected readonly string
            connection;
        protected DbCommand
            command;
        public readonly HashSet<SqlObject>
            ObjectsToReset;
        readonly bool
            shouldClose;
        Dictionary<string, IEnumerable>
            listInputs;

        static Sql()
        {
            defaultConnection = ConfigurationManager.AppSettings["Connection"];
        }

        public Sql(string connection, bool shouldClose)
        {
            this.connection = connection ?? defaultConnection;
            this.shouldClose = shouldClose;

            if (this.connection == null)
                throw new Exception("connection not set");

            this.ObjectsToReset = new HashSet<SqlObject>();

            this.listInputs = new Dictionary<string, IEnumerable>();
        }

        public static Type GetFinalType(Type type)
        {
            return Nullable.GetUnderlyingType(type) ?? type;
        }

        public void Clear()
        {
            this.command.Parameters.Clear();   
            this.listInputs.Clear();
        }

        public void SetTimeout(int seconds)
        {
            this.command.CommandTimeout = seconds;
        }

        public abstract DbParameter AddInput(string key, object value, Type type = null);

        public abstract DbParameter AddOutput(string key, Type type);

        public void AddListInput(string key, IEnumerable values)
        {
            this.listInputs.Add(key, values);
        }

        void ProcessListInput(string key, IEnumerable values, ref string query)
        {
            // list
            int
                i = 0;
            string
                keys = string.Empty;

            foreach (object obj in values)
            {
                string
                    temp = $"auto_{key}_{i++}";

                AddInput(temp, obj);
                keys += ",@" + temp;
            }

            if (i == 0) // list parameter with empty list - add default
            {
                AddInput(key, DBNull.Value);
                return;
            }

            // replace exacly
            key = '@' + key;
            keys = keys.Substring(1);
            i = 0;

            while ((i = query.IndexOf(key, i, StringComparison.OrdinalIgnoreCase)) != -1)
            {
                int
                    n = i + key.Length;

                if (n == query.Length || !(char.IsLetterOrDigit(query[n]) || query[n] == '_'))
                {
                    query = query.Remove(i, key.Length).Insert(i, keys);
                    i += keys.Length;
                }
                else
                    i += key.Length;
            }
        }

        void Open()
        {
            if (this.command.Connection.State.In(ConnectionState.Closed, ConnectionState.Broken))
                using (new Stoper("conn. open"))
                    this.command.Connection.Open();
        }

        public int ExecuteProcedure(string name, bool openTransaction = true)
        {
            Open();

            this.command.CommandType = CommandType.StoredProcedure;
            this.command.CommandText = name;

            bool
                internalTransaction = false;

            if (openTransaction && this.Transaction == null)
            {
                BeginTransaction();
                internalTransaction = true;
            }

            try
            {
                int
                    result;
                
                using (new Stoper("execute procedure", () => name))
                    result = this.command.ExecuteNonQuery();

                if (internalTransaction)
                    Commit();

                return result;
            }
            catch (Exception)
            {
                if (internalTransaction)
                    Rollback();

                throw;
            }
        }

#region Transaction
        public DbTransaction Transaction
        {
            get { return this.command.Transaction; }
        }

        public void BeginTransaction()
        {
            if (this.Transaction != null)
                throw new Exception("transaction already open");

            Open();
            this.command.Transaction = this.command.Connection.BeginTransaction();
        }

        public void Commit()
        {
            if (this.Transaction == null)
                throw new Exception("no transaction to commit");

            foreach (var obj in this.ObjectsToReset)
                obj.ResetChanges();

            this.ObjectsToReset.Clear();

            this.Transaction.Commit();
        }

        public void Rollback()
        {
            if (this.Transaction != null)
                this.Transaction.Rollback();
        }
#endregion

        protected virtual void SetupQuery(string query, params object[] objs)
        {
            query = string.Format(query, objs);

            foreach (var input in this.listInputs)
                ProcessListInput(input.Key, input.Value, ref query);

            this.command.CommandType = CommandType.Text;
            this.command.CommandText = query;
        }

        public DbDataReader ExecuteReader(string query, params object[] objs)
        {
            Open();
            SetupQuery(query, objs);

            using (new Stoper("execute reader", () => this.command.CommandText))
                return this.command.ExecuteReader();
        }

        public object ExecuteScalar(string query, params object[] objs)
        {
            Open();
            SetupQuery(query, objs);

            using (new Stoper("execute scalar", () => this.command.CommandText))
                return this.command.ExecuteScalar();
        }

        public virtual decimal GetIdentity()
        {
            throw new NotSupportedException();
        }

        public int ExecuteNonQuery(string query, params object[] objs)
        {
            Open();
            SetupQuery(query, objs);

            using (new Stoper("execute non-query", () => this.command.CommandText))
                return this.command.ExecuteNonQuery();
        }

        public List<SqlDynamic> LoadDynamicList(string query, params object[] objs)
        {
            Open();
            SetupQuery(query, objs);

            using (new Stoper("load dynamic list", () => this.command.CommandText))
            {
                var
                    list = new List<SqlDynamic>();

                using (var reader = this.command.ExecuteReader())
                    while (reader.Read())
                        list.Add(new SqlDynamic(reader));

                return list;
            }
        }

        public List<TElement> LoadList<TElement>(string query, Func<DbDataReader, TElement> mapF)
        {
            Open();
            SetupQuery(query);

            using (new Stoper("load list", () => this.command.CommandText))
            {
                var
                    list = new List<TElement>();

                using (var reader = this.command.ExecuteReader())
                    while (reader.Read())
                        list.Add(mapF(reader));

                return list;
            }
        }

        public Dictionary<TKey, TValue> LoadDictionary<TKey, TValue>(string query, Func<object, TKey> keyF = null, Func<object, TValue> valueF = null)
        {
            Open();
            SetupQuery(query);

            if (keyF == null)
                keyF = e => (TKey)e;
            if (valueF == null)
                valueF = e => (TValue)e;

            using (new Stoper("load dictionary", () => this.command.CommandText))
            {
                var
                    dict = new Dictionary<TKey, TValue>();

                using (var reader = this.command.ExecuteReader())
                    while (reader.Read())
                        dict.Add(keyF(reader[0]), valueF(reader[1]));

                return dict;
            }
        }

        public virtual string EscapeName(string name)
        {
            return name;
        }

        public void Dispose()
        {
            if (!this.shouldClose)
                return; // if connection was passed from outside

            Rollback();
            this.command.Connection.Close();
        }
    }
}
